drop proc spa_SEC_ValidateForgotPwdRequest
go
/*********************************************************************************************            
* PROCEDURE: spa_SEC_ValidateForgotPwdRequest            
* PURPOSE: Create a new user from FE website            
* NOTES:            
* CREATED: Nha Doan 5/01/2011           
* MODIFIED:             
*/            
/*'-------------------------------------------------------------------------------------------------*            
' Date       | Version | Ticket #    |       Author             | Description            
' ========== | ======= | =========== | =========================| ====================================================================            
' 5/01/2011  |  1.00   | 00000       | Nha Doan                 | CREATED NEW.
'-------------------------------------------------------------------------------------------------*            
*/
/*
Ex:
[spa_SEC_ValidateForgotPwdRequest]            
	 @UserRecId = '{21CAFCEA-8629-4FA9-9DC0-85A98AD344D2}',            
	 @ValidKey ='6E-9B-3A-76-20-AA-F7-7F-36-27-75-15-09-77-EE-B8'    
*/            
create proc [spa_SEC_ValidateForgotPwdRequest]            
 @UserRecId uniqueidentifier,            
 @ValidKey varchar(100), 
 @ASPLog_ClientIPAddr varchar(20) = 'none',                      
 @ASPLog_HostIPAddr varchar(20) = 'none',                      
 @ASPLog_BrowserType varchar(250) = 'none' 

as
/*
return 
	100: successful
	101: invalid user
	102: key is expired
	103: invalid key (used or no request)
*/
declare @ActivationForgotPwdDuration int,
	@ClientId int,
	@CurrentValidKey varchar(100),
	@UserPasswordType int,                      
	@UserPasswordDTS datetime,                      
	@LoginAttempts int,                      
	@LastLoginDTS datetime,
	@UserStatus varchar(1),                      
	@LoginName varchar(30),                      
	@LoginAction int,                      
	@LoginActionText varchar(100),                      
	@UserNameFirst varchar(30),                      
	@UserNameLast  varchar(30),
	@ReturnCode int,
	@ExecDTS datetime
set @ExecDTS = getdate()
set @ClientId = null
set @CurrentValidKey = null
set @ActivationForgotPwdDuration = 3
select  @ClientId = ClientID,
	@CurrentValidKey = UserPasswordTmp,
 	@UserPasswordDTS = UserPasswordDTS,
 	@LoginAttempts = LoginAttempts,
 	@LastLoginDTS = LastLoginDTS,
 	@UserStatus = Status,
 	@UserNameFirst = UserNameFirst,
 	@UserNameLast = UserNameLast
from SEC_Users (nolock)
where 
 RecId = @UserRecId

select @ActivationForgotPwdDuration = cast([Value] as int) from REF_Configs (nolock) where [Key] = 'ActivationForgotPwdDuration' and ClientId = @ClientId

if @ClientId is null
begin
	set @ReturnCode = 101 --invalid user
end
else if @CurrentValidKey is null
begin
	set @ReturnCode = 103 --invalid key
end
else if @ExecDTS > dateadd(dd,@ActivationForgotPwdDuration,@UserPasswordDTS)
begin
	set @ReturnCode = 102 -- key expired
end
else if @CurrentValidKey <> @ValidKey
begin
	set @ReturnCode = 103 --invalid key
end
else 
begin
set @ReturnCode = 100 --ok
update SEC_Users
set UserPasswordTmp = null,
	UserPasswordDTS = @ExecDTS
where RecId = @UserRecId
end


insert into LOG_UserLoginTransactions (                      
 TransDTS,
 ReturnCode,                      
 ClientId,
 LoginName,
 UserPassword,                      
 UserNameFirst,                      
 UserNameLast,                      
 UserPasswordType,                      
 UserPasswordDTS,                      
 LoginAttempts,                      
 LastLoginDTS,                      
 UserStatus,                       
 ClientIPAddr,                      
 HostIPAddr,                      
 BrowserType                    
)                     
values (                     
 @ExecDTS,
 @ReturnCode,                      
 @ClientId,                     
 @LoginName,
 @CurrentValidKey+'|'+@ValidKey,
 @userNameFirst,                     
 @userNameLast,                     
 @UserPasswordType,                     
 @UserPasswordDTS,                           
 @LoginAttempts,                    
 @LastLoginDTS,                     
 @UserStatus,                     
 @ASPLog_ClientIPAddr,                     
 @ASPLog_HostIPAddr,                     
 @ASPLog_BrowserType  
)
select   @ReturnCode as ReturnCode                  
GO